<?php
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel.php");
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel/Writer/Excel2007.php");

class Silk_Zgb_Block_Report_Shop extends Mage_Core_Block_Template
{
    public function getTeamCustomer()
    {
        $collection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToFilter('group_id', array('eq'=>4));

        $session = Mage::getSingleton('admin/session');
        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
        }

        $ids = array(0);
        foreach($collection as $customer)
        {
            $ids[] = $customer->getId();
        }
        return $ids;
    }

    public function getItemCollection()
    {   $status = null;
        $startDate = $this->getRequest()->getParam('start_date');
        $endDate = $this->getRequest()->getParam('end_date');
        $shopName = trim($this->getRequest()->getParam('shop_name'));
        $startQuote = $this->getRequest()->getParam('start_quote');
        $endQuote = $this->getRequest()->getParam('end_quote');
        $startTotal = $this->getRequest()->getParam('start_total');
        $endTotal = $this->getRequest()->getParam('end_total');
        $websiteId = $this->getRequest()->getParam('store_id');

        
        $export = $this->getRequest()->getParam('export');
        $apply = $this->getRequest()->getParam('apply');

        $session = Mage::getSingleton('admin/session');
        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
        }
        
        $ids = array();
        $storeIds = array();

        $teams = implode(',',$this->getTeamCustomer());

        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');

        $dsql = "";
        $cesql = "";

        

        //$sql = "delete from zgb_report_shop";
        //$db->exec($sql);
        //$sql = "INSERT INTO zgb_report_shop SELECT * FROM (SELECT customer_id,COUNT(DISTINCT(quote_id)) AS quote_total,COUNT(DISTINCT(entity_id)) AS order_total,SUM(subtotal) AS subtotal FROM `sales_flat_order` o WHERE o.status!='canceled' and o.customer_id not in ({$teams}) {$dsql} GROUP BY customer_id) orders RIGHT JOIN (SELECT ce.`entity_id` AS shop_id,ce.created_at,cev.`value` AS shop_name, ce.website_id FROM `customer_entity` ce LEFT JOIN `customer_entity_varchar` cev ON ce.entity_id=cev.entity_id AND cev.attribute_id=7 ) customers ON orders.customer_id=customers.shop_id ORDER BY shop_name ASC, customers.created_at ASC";
        //$db->exec($sql);

        //$sql = "select * from zgb_report_shop where 1=1";

        $sql = "SELECT c.entity_id,c.store_name,c.website_name,c.created_at,COUNT(o.entity_id) AS order_total, SUM(o.grand_total_ex) AS grand_total,c.name,c.mobile 
        FROM zgb_store c LEFT JOIN zgb_order o ON c.`entity_id`=o.customer_id where c.is_active=1 and c.entity_id not in({$teams})";

        if($startDate)
        {
            $startDate = "{$startDate} 00:00:00";
            $sql .= " and o.created_at>= '{$startDate}'";
        }

        if($endDate)
        {
            $endDate = "{$endDate} 23:59:59";
            $sql .= " and o.created_at<= '{$endDate}'";
        }

        if($websiteId)
        {
            $sql .= " and c.website_id='{$websiteId}'";
        }
        

        if($shopName)
        {
            $sql .= " and c.store_name LIKE '%{$shopName}%'";
        }


        if($status)
        {
            $sql .= " and status='{$status}'";
        }

        $sql .= " GROUP BY c.`entity_id`";



        //$sql .= "having count(o.entity_id)>=2";

        $having = '';

        if($startQuote || (is_numeric($startQuote)&&intval($startQuote)===0))
        {
            $having .= " and count(o.entity_id)>= {$startQuote}";
        }

        if($endQuote || (is_numeric($endQuote)&&intval($endQuote)===0))
        {
            $having .= " and count(o.entity_id)<= {$endQuote}";
        }

        if($startTotal)
        {
            $having .= " and sum(o.grand_total_ex)>= {$startTotal}";
        }

        if($endTotal)
        {
            $having .= " and sum(o.grand_total_ex)<= {$endTotal}";
        }

        $having = trim($having);
        $having = trim($having,'and');

        if($having) $having = " having {$having}";

        $sql .= $having;

        $sql .= " ORDER BY CONVERT( store_name USING gbk ) COLLATE gbk_chinese_ci ASC, created_at asc";

        //echo $sql;
        if($export || $apply)
        {
            $query = $db->query($sql);
            $items = $query->fetchAll();
        }
        else
        {
            $items = array();
        }

        return $items;
    }

    public function exportToExcel()
    {
        $items = $this->getItemCollection();
        $objPHPExcel  = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->setTitle("终端用户统计报表");
        $sheet->getColumnDimension('A')->setWidth(30);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(16);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(30);
        $sheet->getColumnDimension('G')->setWidth(16);
        $sheet->getColumnDimension('H')->setWidth(16);
        $sheet->getColumnDimension('I')->setWidth(12);
        $sheet->getColumnDimension('J')->setWidth(12);
        $sheet->getColumnDimension('K')->setWidth(12);

        $sheet->getStyle('A1')->getFont()->setSize(13);
        $sheet->getStyle('A1')->getFont()->setBold(true);

        $sheet->getStyle('B1')->getFont()->setSize(13);
        $sheet->getStyle('B1')->getFont()->setBold(true);

        $sheet->getStyle('C1')->getFont()->setSize(13);
        $sheet->getStyle('C1')->getFont()->setBold(true);

        // $sheet->getStyle('D1')->getFont()->setSize(13);
        // $sheet->getStyle('D1')->getFont()->setBold(true);

        $sheet->getStyle('D1')->getFont()->setSize(13);
        $sheet->getStyle('D1')->getFont()->setBold(true);

        $sheet->getStyle('E1')->getFont()->setSize(13);
        $sheet->getStyle('E1')->getFont()->setBold(true);

        $sheet->getStyle('F1')->getFont()->setSize(13);
        $sheet->getStyle('F1')->getFont()->setBold(true);

        $sheet->getStyle('G1')->getFont()->setSize(13);
        $sheet->getStyle('G1')->getFont()->setBold(true);

        $sheet->getStyle('H1')->getFont()->setSize(13);
        $sheet->getStyle('H1')->getFont()->setBold(true);

        $sheet->getStyle('I1')->getFont()->setSize(13);
        $sheet->getStyle('I1')->getFont()->setBold(true);

        $sheet->getStyle('J1')->getFont()->setSize(13);
        $sheet->getStyle('J1')->getFont()->setBold(true);

        $sheet->getStyle('K1')->getFont()->setSize(13);
        $sheet->getStyle('K1')->getFont()->setBold(true);


        $sheet->getRowDimension('1')->setRowHeight(20);

        $styleThinBlackBorderOutline = array(
            'borders' => array (
               'outline' => array (
                  'style' => PHPExcel_Style_Border::BORDER_THIN, 
                  'color' => array ('argb' => 'FF000000'),
              ),
           ),
        );

        $statuses = array('pending'=>"待处理",'processing'=>"处理中",'complete'=>"已完成",'cancel'=>"已取消");

        $row = 1;
        $sheet->setCellValue("A{$row}","店铺名");
        $sheet->setCellValue("B{$row}","区域");
        $sheet->setCellValue("C{$row}","注册日期");
        $sheet->setCellValue("D{$row}","下单次数");
        // $sheet->setCellValue("D{$row}","下单数量");
        $sheet->setCellValue("E{$row}","订单金额");
        $sheet->setCellValue("F{$row}","首次下单");
        $sheet->setCellValue("G{$row}","最近下单");
        $sheet->setCellValue("H{$row}","未下单时间");
        $sheet->setCellValue("I{$row}","联系人");
        $sheet->setCellValue("J{$row}","联系电话");
        $sheet->setCellValue("K{$row}","备注");

        $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
        // $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("G{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("H{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("I{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("J{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("K{$row}")->applyFromArray($styleThinBlackBorderOutline);
        
        $row++;
        foreach($items as $item)
        {
            $orders = Mage::getModel('sales/order')
                    ->getCollection()
                    ->addFieldToFilter('customer_id', array('eq'=>$item['entity_id']))
                    ->setOrder('created_at','asc');

            $sheet->setCellValue("A{$row}",$item['store_name']);
            $sheet->setCellValue("B{$row}",$item['website_name']);
            $sheet->setCellValue("C{$row}",$item['created_at']);
            $sheet->setCellValue("D{$row}",$item['order_total']);
            // $sheet->setCellValue("D{$row}",$item['order_total']);
            $sheet->setCellValue("E{$row}",$item['grand_total']);
            $sheet->setCellValue("F{$row}",$this->convertDate($orders->getFirstItem()->getCreatedAt()));
            $sheet->setCellValue("G{$row}",$this->convertDate($orders->getLastItem()->getCreatedAt()));
            $sheet->setCellValue("H{$row}",$this->aband($orders->getLastItem()->getCreatedAt()));
            $sheet->setCellValue("I{$row}",$item['name']);
            $sheet->setCellValue("J{$row}",$item['mobile']);
            $sheet->setCellValue("K{$row}",'');
            

            $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
            // $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("G{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("H{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("I{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("J{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("K{$row}")->applyFromArray($styleThinBlackBorderOutline);

            $row++;
        }
        //$sheet->setCellValue("F{$row}", "=SUM(F2:F".($row-1).")");
        //$sheet->setCellValue("G{$row}", "=SUM(G2:G".($row-1).")");
        return $objWriter;
    }

    public function convertDate($date)
    {
        $timestamp = strtotime($date);
        return date("Y年m月d日", $timestamp+3600*8);
    }

    public function aband($date)
    {
        if(!$date) return '';
        $timestamp = strtotime($date);
        $now = strtotime(Mage::getSingleton('core/date')->gmtDate());
        //if(($now-$timestamp)>86400)
        return ceil(($now-$timestamp)/86400);
    }       



    public function getDealerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::DEALER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }

    public function getManufacturerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::MANUFACTURER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        echo $sql;
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }
}